package com.zheng.admin.mapper;

import com.zheng.admin.form.PagingReq;
import com.zheng.admin.model.Perm;
import com.zheng.admin.model.Role;
import com.zheng.admin.model.User;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * Created by zcz on 2017/10/26.
 */
@Mapper
public interface UserMapper {

    @Select("select * from user where id=#{id}")
    User getUserById(@Param("id")Integer id);

    @Select("select * from user where phone=#{phone}")
    User getUserByPhone(@Param("phone") String phone);

    @Update({
            "<script>",
            "update user set status='deleted' where id in ",
            "<foreach collection =\"ids\" item=\"id\" index= \"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{id}",
            "</foreach>",
            "</script>"
    })
    Integer deleteUsers(@Param("ids")Integer... ids);

    @Update({
            "<script>",
            "update role set status='deleted' where id in ",
            "<foreach collection =\"ids\" item=\"id\" index= \"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{id}",
            "</foreach>",
            "</script>"
    })
    Integer deleteRoles(@Param("ids")Integer... ids);

    @Update({
            "<script>",
            "update perm set status='deleted' where id in ",
            "<foreach collection =\"ids\" item=\"id\" index= \"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{id}",
            "</foreach>",
            "</script>"
    })
    Integer deletePerms(@Param("ids")Integer... ids);

    @Delete("delete from user_role where userId=#{userId} and roleId=#{roleId} limit 1")
    Integer deleteUserRole(@Param("userId")Integer userId, @Param("roleId")Integer roleId);

    @Insert("insert into user_role(userId, roleId) values(#{userId},#{roleId})")
    Integer addUserRole(@Param("userId")Integer userId, @Param("roleId")Integer roleId);

    @Select("select r.* from role r, user_role ur where r.id=ur.roleId and ur.userId=#{userId} and r.status='normal'")
    List<Role> getUserRoleByUserId(@Param("userId")Integer userId);

    @Select("select r.* from role r where r.status='normal'")
    List<Role> getAllRoles();

    @Select("select * from role where id=#{id}")
    Role getRoleById(@Param("id")Integer id);

    @Select("select * from perm where id=#{id}")
    Perm getPermById(@Param("id")Integer id);

    @Select({
            "select p.* from role r, user_role ur, role_perm rp,perm p where r.id = ur.roleId ",
            " and ur.roleId = rp.roleId",
            " and rp.permId = p.id",
            " and ur.userId = #{userId}",
            " and r.status='normal'",
            " and p.status='normal'",
    })
    List<Perm> findPerms(@Param("userId")Integer userId);

    @Insert({
        "insert into user(phone, email, name, password, status) values (#{u.phone}, #{u.email}, #{u.name}, #{u.password}, #{u.status})"
    })
    @SelectKey(resultType = Integer.class, keyColumn = "id", keyProperty = "u.id", before = false, statement = "SELECT LAST_INSERT_ID()")
    int insertUser(@Param("u") User user);

    @Update("update user set phone=#{u.phone}, email=#{u.email}, name=#{u.name}, status=#{u.status} where id=#{u.id} limit 1")
    int updateUser(@Param("u") User user);

    @Insert({
            "insert into user_role (userId, roleId) values (#{userId}, #{roleId})"
    })
    int insertUserRole(@Param("userId") Integer userId, @Param("roleId") Integer roleId);

    @Insert({
            "insert into role (name, info, status) values (#{r.name}, #{r.info}, #{r.status})"
    })
    @SelectKey(resultType = Integer.class, keyColumn = "id", keyProperty = "r.id", before = false, statement = "SELECT LAST_INSERT_ID()")
    int insertRole(@Param("r") Role role);

    @Update("update role set name=#{r.name}, info=#{r.info}, status=#{r.status} where id=#{r.id} limit 1")
    int updateRole(@Param("r") Role role);

    @Insert({
            "insert into perm (`type`, name, status, info) values (#{p.type}, #{p.name}, #{p.status}, #{p.info})"
    })
    @SelectKey(resultType = Integer.class, keyProperty = "p.id", before = false, statement = "SELECT LAST_INSERT_ID()")
    int insertPerm(@Param("p") Perm perm);

    @Update("update perm set `type`=#{p.type}, name=#{p.name}, info=#{p.info}, status=#{p.status} where id=#{p.id} limit 1")
    int updatePerm(@Param("p") Perm perm);

    @Insert({
            "insert into role_perm (permId, roleId) values (#{permId}, #{roleId})"
    })
    int insertRolePerm(@Param("permId") Integer permId, @Param("roleId") Integer roleId);

    @Select({
            "<script>",
            "select * from user where id in (",
            "   select id from user where 1=1 and status='normal' ",
            "<foreach collection =\"req.columns\" item=\"column\" index= \"index\" separator =\"\"> ",
            "<if test=\"column.search.value != null and column.search.value != ''\">",
            " and ${column.data} like \"%\"#{column.search.value}\"%\" ",
            "</if>",
            "</foreach>",
            ") order by ",
            "<foreach collection =\"req.order\" item=\"od\" index= \"index\" separator =\",\"> ",
            "${req.columns.get(od.column).data} ${od.dir}",
            "</foreach>",
            " limit ${req.start},${req.length} ",
            "</script>"
    })
    List<User> queryUserByPaging(@Param("req")PagingReq req);

    @Select({
            "<script>",
            "select count(id) from user where 1=1 ",
            "</script>"
    })
    Long queryUserCountByPaging(@Param("req")PagingReq req);

    @Select({
            "<script>",
            "select * from role where id in (",
            "   select id from role where 1=1 and status='normal' ",
            "<foreach collection =\"req.columns\" item=\"column\" index= \"index\" separator =\"\"> ",
            "<if test=\"column.search.value != null and column.search.value != ''\">",
            " and ${column.data} like \"%\"#{column.search.value}\"%\" ",
            "</if>",
            "</foreach>",
            ") order by ",
            "<foreach collection =\"req.order\" item=\"od\" index= \"index\" separator =\",\"> ",
            "${req.columns.get(od.column).data} ${od.dir}",
            "</foreach>",
            " limit ${req.start},${req.length} ",
            "</script>"
    })
    List<Role> queryRoleByPaging(@Param("req")PagingReq req);

    @Select({
            "<script>",
            "select count(id) from role where 1=1 and status='normal'",
            "</script>"
    })
    Long queryRoleCountByPaging(@Param("req")PagingReq req);

    @Select({
            "<script>",
            "select * from role where status='normal' and id in (",
            "   select roleId from role_perm where permId=#{permId}  ",
            ") order by ",
            "<foreach collection =\"req.order\" item=\"od\" index= \"index\" separator =\",\"> ",
            "${req.columns.get(od.column).data} ${od.dir}",
            "</foreach>",
            " limit ${req.start},${req.length} ",
            "</script>"
    })
    List<Role> queryPermRoleByPaging(@Param("permId")Integer permId, @Param("req")PagingReq req);

    @Select({
            "<script>",
            "select count(r.id) from role r, role_perm rp where r.id=rp.roleId and r.status='normal' and rp.permId=#{permId}",
            "</script>"
    })
    Long queryPermRoleCountByPaging(@Param("permId")Integer permId, @Param("req")PagingReq req);

    @Select({
            "<script>",
            "select * from perm where id in (",
            "   select id from perm where 1=1 and status='normal' ",
            "<foreach collection =\"req.columns\" item=\"column\" index= \"index\" separator =\"\"> ",
            "<if test=\"column.search.value != null and column.search.value != ''\">",
            " and ${column.data} like \"%\"#{column.search.value}\"%\" ",
            "</if>",
            "</foreach>",
            ") order by ",
            "<foreach collection =\"req.order\" item=\"od\" index= \"index\" separator =\",\"> ",
            "${req.columns.get(od.column).data} ${od.dir}",
            "</foreach>",
            " limit ${req.start},${req.length} ",
            "</script>"
    })
    List<Perm> queryPermByPaging(@Param("req")PagingReq req);

    @Select({
            "<script>",
            "select count(id) from perm where 1=1 and status='normal' ",
            "</script>"
    })
    Long queryPermCountByPaging(@Param("req")PagingReq req);

    @Select({
            "<script>",
            "select p.* from perm p, role_perm rp where p.id=rp.permId and rp.roleId and rp.roleId=#{roleId} ",
            " order by ",
            "<foreach collection =\"req.order\" item=\"od\" index= \"index\" separator =\",\"> ",
            "${req.columns.get(od.column).data} ${od.dir}",
            "</foreach>",
            " limit ${req.start},${req.length} ",
            "</script>"
    })
    List<Perm> queryRolePermByPaging(@Param("roleId")Integer roleId, @Param("req")PagingReq req);

    @Select({
            "<script>",
            "select count(p.id) from perm p, role_perm rp where p.id=rp.permId and rp.roleId and rp.roleId=#{roleId} and p.status='normal'",
            "</script>"
    })
    Long queryRolePermCountByPaging(@Param("roleId")Integer roleId, @Param("req")PagingReq req);

    @Select({
            "<script>",
            "select p.* from perm p, role_perm rp where p.id=rp.permId and p.status='normal' and rp.roleId in (",
            "   select ur.roleId from user u, user_role ur where u.id=ur.roleId  and rp.roleId=ur.roleId and u.id=#{userId} and u.status='normal'",
            ") order by ",
            "<foreach collection =\"req.order\" item=\"od\" index= \"index\" separator =\",\"> ",
            "${req.columns.get(od.column).data} ${od.dir}",
            "</foreach>",
            " limit ${req.start},${req.length} ",
            "</script>"
    })
    List<Perm> queryUserPermByPaging(@Param("userId")Integer userId, @Param("req")PagingReq req);

    @Select({
            "<script>",
            "select count(p.id) from perm p, role_perm rp where p.id=rp.permId and p.status='normal' and rp.roleId in (",
            "   select ur.roleId from user u, user_role ur where u.id=ur.roleId  and rp.roleId=ur.roleId and u.id=#{userId} and u.status='normal'",
            ") ",
            "</script>"
    })
    Long queryUserPermCountByPaging(@Param("userId")Integer userId, @Param("req")PagingReq req);

    @Delete("delete from role_perm where permId=#{permId} and roleId=#{roleId} limit 1")
    Integer deleteRolePerm(@Param("permId")Integer permId, @Param("roleId")Integer roleId);

    @Insert("insert into role_perm(permId, roleId) values(#{permId},#{roleId})")
    Integer addRolePerm(@Param("permId")Integer permId, @Param("roleId")Integer roleId);

    @Select("select permId from role_perm where roleId=#{roleId}")
    List<Integer> queryRolePermIds(@Param("roleId")Integer roleId);

    @Select("select roleId from role_perm where permId=#{permId}")
    List<Integer> queryPermRoleIds(@Param("permId")Integer permId);

    @Select({
            "<script>",
            "select u.* from user u, user_role ru where u.status='normal' and u.id=ru.userId and ru.roleId=#{roleId} ",
            " order by ",
            "<foreach collection =\"req.order\" item=\"od\" index= \"index\" separator =\",\"> ",
            "${req.columns.get(od.column).data} ${od.dir}",
            "</foreach>",
            " limit ${req.start},${req.length} ",
            "</script>"
    })
    List<User> queryRoleUserByPaging(@Param("roleId")Integer roleId, @Param("req")PagingReq req);

    @Select({
            "<script>",
            "select count(u.id) from user u, user_role ru where u.id=ru.userId and ru.roleId=#{roleId} and u.status='normal'",
            "</script>"
    })
    Long querRoleUserCountByPaging(@Param("roleId")Integer roleId, @Param("req")PagingReq req);

    @Select("select userId from user_role where roleId=#{roleId}")
    List<Integer> queryRoleUserIds(@Param("roleId")Integer roleId);
}
